﻿/*ShopReport.cs
 * 
 * 2011-10-11
 * JF
 * 功能介绍
 * 1，eShop报表功能的DAL层
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using EShop.Common;

namespace EShop.DAL
{
    public class ShopReportDAL:BaseDAL
    {

        /// <summary>
        /// 查询指定日期[&员工]的销售报表
        /// </summary>
        /// <param name="Day">被查询的日期</param>
        /// <param name="EmployeeID">被查询员工的工号</param>
        /// <returns></returns>
        public DataSet DailySelledReport(DateTime Day, string EmployeeID)
        {
            string strUSP = "USP_MerchandiseTodaySelled_Query";
            try
            {
                DataSet ds = new DataSet();
                if (string.IsNullOrEmpty(EmployeeID))
                {
                    SqlParameter[] parameter ={
                                        new SqlParameter("@SellDate", SqlDbType.DateTime)
                                      };
                    parameter[0].Value = Day;
                    ds = DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter);
                }
                else
                {
                    SqlParameter[] parameter ={
                                        new SqlParameter("@EmployeeID", SqlDbType.VarChar, 12),
                                        new SqlParameter("@SellDate", SqlDbType.DateTime)
                                      };
                    parameter[0].Value = EmployeeID;
                    parameter[1].Value = Day;
                    ds = DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter);
                }
                return ds;
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 返回指定日期有销售记录的员工
        /// </summary>
        /// <param name="selledDate">指定日期</param>
        /// <returns></returns>
        public Dictionary<object, object> GetSelledEmployee(DateTime selledDate)
        {
            Dictionary<object, object> o = new Dictionary<object, object>();
            string strUSP = "USP_SelledEmployee";
            try
            {
                SqlParameter parameter = new SqlParameter("@QueryDate", selledDate);
                DataTable dt = DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
                int iRowsCount = dt.Rows.Count;
                if (iRowsCount > 0)
                {
                    for (int i = 0; i < iRowsCount; i++)
                    {
                        o.Add(dt.Rows[i]["EmployeeID"], dt.Rows[i]["FullName"]);
                    }
                }
                return o;
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 获取趋势图
        /// </summary>
        /// <param name="type">0,日；1：月</param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetTrendChart(int type, DateTime beginDate, DateTime endDate)
        {
            string strUSP = "USP_TrendChart";
            try
            {
                SqlParameter[] parameters = {
                                          new SqlParameter("@Type",SqlDbType.Int),
                                          new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                          new SqlParameter("@EndDate",SqlDbType.DateTime)
                                      };
                parameters[0].Value = type;
                parameters[1].Value = beginDate;
                parameters[2].Value = endDate;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameters).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 获取指定时间段内商品的销售数量
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetHotMerchandise(DateTime beginDate, DateTime endDate)
        {
            string strUSP = "USP_HotMerchandiseQuery";
            try
            {
                SqlParameter[] parameters ={
                                          new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                          new SqlParameter("@EndDate",SqlDbType.DateTime)
                                      };
                parameters[0].Value = beginDate;
                parameters[1].Value = endDate;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameters).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 查询利润
        /// </summary>
        /// <param name="iType">
        /// 0，按年份查看利润
        /// 1，按月份查看利润
        /// 2，按日期查看利润
        /// 3，查看某单号的具体详情
        /// </param>
        /// <param name="strYear">年份yyyy</param>
        /// <param name="strMonth">月份MM</param>
        /// <param name="strDay">日期dd</param>
        /// <param name="strOrderID">OrderID</param>
        /// <returns></returns>
        public DataTable GetProfits(int iType, string strYear, string strMonth, string strDay, string strOrderID)
        {
            string strUSP = "USP_Profits_Query";
            try
            {
                SqlParameter[] parameters ={
                                              new SqlParameter("@Type",SqlDbType.Int),
                                              new SqlParameter("@Year",SqlDbType.VarChar,4),
                                              new SqlParameter("@Month",SqlDbType.VarChar,2),
                                              new SqlParameter("@Day",SqlDbType.VarChar,2),
                                              new SqlParameter("@OrderID",SqlDbType.VarChar,50)
                                          };
                parameters[0].Value = iType;
                parameters[1].Value = strYear;
                parameters[2].Value = strMonth;
                parameters[3].Value = strDay;
                parameters[4].Value = strOrderID;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameters).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }
    }
}
